﻿using System;
using System.Collections.Generic;
using Model.DTO;
using Model.Domain_Model;
using System.Data.SqlClient;
using System.Data;
using Repository.DatabaseType;

namespace Repository
{
    public class ClientTable: BaseClientTable
    {
        private SqlConnection connection = null;
        private SqlServer2008 database = null;

        public ClientTable()
        {
        }

        public ClientTable(SqlConnection connection, SqlServer2008 database)
        {
            this.connection=connection;
            this.database = database;
        }

        public override void NewClient(Client client)
        {
            base.NewClient(client);
            database.OpenConnection();
            SqlCommand cmd = new SqlCommand(SqlDefinitions.Client.newClient, connection);
            cmd.Parameters.AddWithValue("@val1", client.nameClient);
            cmd.Parameters.AddWithValue("@val2", client.surnameClient);
            cmd.Parameters.AddWithValue("@val3", client.nameCorporation);
            cmd.Parameters.AddWithValue("@val4", client.isProtected);
            cmd.Parameters.AddWithValue("@val5", client.descriptionClient);
            cmd.Parameters.AddWithValue("@val6", client.inventedName);
            cmd.Parameters.AddWithValue("@val7", client.phoneNumber);
            cmd.Parameters.AddWithValue("@val8", client.email);
            cmd.Parameters.AddWithValue("@val9", client.dateInsert);
            cmd.ExecuteNonQuery();
            database.CloseConnection();
        }

        public override IList<Client> GetClientList()
        {
            IList<Client> clientList = new List<Client>();
            database.OpenConnection();
            SqlCommand cmd = new SqlCommand(SqlDefinitions.Client.getClientList, connection);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
                clientList.Add(new Client { idClient = reader.GetInt32(0), nameClient = reader.GetString(1), surnameClient = reader.GetString(2), nameCorporation = reader.GetString(3), isProtected = reader.GetBoolean(4), descriptionClient = reader.GetString(5), inventedName = reader.GetString(6), phoneNumber = reader.GetString(7), email = reader.GetString(8), dateInsert = reader.GetDateTime(9) });
            database.CloseConnection();
            return clientList;
        }

        public override void UpdateClient(Client client)
        {
            base.UpdateClient(client);
            database.OpenConnection();
            SqlCommand cmd = new SqlCommand(SqlDefinitions.Client.updateClient, connection);
            cmd.Parameters.AddWithValue("@val1", client.nameClient);
            cmd.Parameters.AddWithValue("@val2", client.surnameClient);
            cmd.Parameters.AddWithValue("@val3", client.nameCorporation);
            cmd.Parameters.AddWithValue("@val4", client.isProtected);
            cmd.Parameters.AddWithValue("@val5", client.descriptionClient);
            cmd.Parameters.AddWithValue("@val6", client.inventedName);
            cmd.Parameters.AddWithValue("@val7", client.phoneNumber);
            cmd.Parameters.AddWithValue("@val8", client.email);
            cmd.Parameters.AddWithValue("@val9", client.dateInsert);
            cmd.Parameters.AddWithValue("@val11", client.idClient);
            cmd.ExecuteNonQuery();
            database.CloseConnection();
        }

        public override void DeleteClient(Client client)
        {
            base.DeleteClient(client);
            database.OpenConnection();
            SqlCommand cmd = new SqlCommand(SqlDefinitions.Client.clientDelete, connection);
            cmd.Parameters.AddWithValue("@val1", client.idClient);
            cmd.ExecuteNonQuery();
            database.CloseConnection();
        }

        public override IList<Client> SearchClient(string nameClient = "", string surnameClient = "", string inventedName = "", string nameCorp = "")
        {
            IList<Client> clientList = new List<Client>();
            SqlCommand cmd = new SqlCommand(SqlDefinitions.Client.searchClient, connection);
            database.OpenConnection();
            cmd.Parameters.AddWithValue("@nameClient", nameClient);
            cmd.Parameters.AddWithValue("@surnameClient", surnameClient);
            cmd.Parameters.AddWithValue("@inventedName", inventedName);
            cmd.Parameters.AddWithValue("@nameCorp", nameCorp);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
                clientList.Add(new Client { idClient = reader.GetInt32(0), nameClient = reader.GetString(1), surnameClient = reader.GetString(2), nameCorporation = reader.GetString(3), isProtected = reader.GetBoolean(4), descriptionClient = reader.GetString(5), inventedName = reader.GetString(6), phoneNumber = reader.GetString(7), email = reader.GetString(8), dateInsert = reader.GetDateTime(9) });
            database.CloseConnection();
            return clientList;
        }

        public override int CountResult(string nameClient = "", string surnameClient = "", string inventedName = "", string nameCorp = "")
        {
            SqlCommand cmd = new SqlCommand(SqlDefinitions.Client.countResultByCriteria, connection);
            cmd.Parameters.AddWithValue("@nameCorp", nameCorp);
            cmd.Parameters.AddWithValue("@nameClient", nameClient);
            cmd.Parameters.AddWithValue("@inventedName", inventedName);
            cmd.Parameters.AddWithValue("@surnameClient", surnameClient);
            database.OpenConnection();
            var affected = cmd.ExecuteScalar();
            database.CloseConnection();
            return (int)affected;
        }
    }
}
